package com.engine.budget.cmd;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.weaver.general.TimeUtil;
import org.apache.commons.lang.StringUtils;
import weaver.conn.RecordSet;
import weaver.hrm.User;
import weaver.soa.workflow.request.MainTableInfo;
import weaver.soa.workflow.request.Property;
import weaver.soa.workflow.request.RequestInfo;
import weaver.soa.workflow.request.RequestService;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.*;

/**
 * 业务项目管理平台
 * 已生效项目信息调整接口
 * 预算关闭单接口
 * 年度预算调整接口
 */
public class AnnualBudgetAdjustmentCmd extends AbstractCommonCommand<Map<String,Object>> {
    @Override
    public BizLogContext getLogContext() {
        return null;
    }
    public AnnualBudgetAdjustmentCmd(User user, Map<String,Object> params) {
        this.user = user;
        this.params = params;
    }
    public String Createrequest(int workflowid,int userid,String title,String IsNextFlow,Map map){
        String newrequestid="";
        RequestInfo req=new RequestInfo();
        req.setCreatorid(userid+"");  //创建人
        req.setWorkflowid(workflowid+"");//流程id
        req.setDescription(title);  //流程标题
        req.setIsNextFlow(IsNextFlow); //是否默认流转到下一节点
        MainTableInfo maintable=new MainTableInfo();

        Set<String> keySet = map.keySet();
        Property[] property = new Property[keySet.size()];
        int i= 0;
        Property p = null;
        for(String key : keySet){
            p = new Property();
            p.setName(key);
            p.setValue(map.get(key).toString());
            property[i] = p;
            i++;
        }

        maintable.setProperty(property);

        req.setMainTableInfo(maintable);
        req.setRequestlevel("0");
        RequestService service=new RequestService();
        try {
            newrequestid=service.createRequest(req);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return newrequestid;
    }
    /**
     * @param commandContext
     * @return
     */
    @Override
    public Map<String, Object> execute(CommandContext commandContext) {
        JSONObject jsonObject = JSONObject.parseObject(JSONObject.toJSONString(params));
        JSONObject res = new JSONObject();

        JSONObject result = new JSONObject();

        JSONArray data = new JSONArray();
        JSONArray objects = jsonObject.getJSONArray("params");
        Iterator<Object> iterator = objects.iterator();
        while (iterator.hasNext()){
            JSONObject next = (JSONObject) iterator.next();

            JSONArray projectList = next.getJSONArray("projectList");
            Iterator<Object> projectListIterator = projectList.iterator();
            while (projectListIterator.hasNext()){
                JSONObject project = (JSONObject) projectListIterator.next();
                String projectNumber = project.getString("projectNumber");
//            String projectName = project.getString("projectName");
                JSONObject number = selectUfYSXMDBByProjectNumber(projectNumber);
                if (number.isEmpty()){
                    project.put("isSuccess",false);
                    project.put("msg","未查询到项目！");
                    data.add(project);
                    break;
                }
                //预算事项编码
                String yssx = number.getString("yssx");
                JSONObject transaction = getTransactionBYId(yssx);
                if (transaction.isEmpty()){
                    project.put("isSuccess",false);
                    project.put("msg","未查询到事项！");
                    data.add(project);
                    break;
                }
                //3.4.5已生效项目信息调整申请单
                if(project.getString("flag").equals("2")){
                    BigDecimal projectTotalAmount = project.getBigDecimal("projectTotalAmount");
                    //项目立项金额:资本化
                    BigDecimal projectCapitalization = project.getBigDecimal("projectCapitalization");
                    //项目立项金额:系统运营费
                    BigDecimal projectSystemCosts = project.getBigDecimal("projectSystemCosts");
                    //项目立项金额:技术开发类费用
                    BigDecimal projectResearchDev = project.getBigDecimal("projectResearchDev");
                    //项目立项金额:修理维护费
                    BigDecimal projectRepair = project.getBigDecimal("projectRepair");
                    //项目立项金额:低值易耗品摊销
                    BigDecimal projectLowValue = project.getBigDecimal("projectLowValue");

                    ArrayList<String> strings = new ArrayList<>();
                    strings.add("项目立项金额:资本化");
                    strings.add("项目立项金额:总金额");
                    strings.add("项目立项金额:系统运营费");
                    strings.add("项目立项金额:技术开发类费用");
                    strings.add("项目立项金额:修理维护费");
                    strings.add("项目立项金额:低值易耗品摊销");
                    for (String yskm : strings) {
                        JSONObject ysxmdb = selectUfYSXMDBByProjectNumber2(yskm,number.getString("id"));
                        BigDecimal money = BigDecimal.ZERO;
                        if (yskm.equals("项目立项金额:资本化")){money = projectCapitalization;}
                        if (yskm.equals("项目立项金额:系统运营费")){money = projectSystemCosts;}
                        if (yskm.equals("项目立项金额:技术开发类费用")){money = projectResearchDev;}
                        if (yskm.equals("项目立项金额:修理维护费")){money = projectRepair;}
                        if (yskm.equals("项目立项金额:低值易耗品摊销")){money = projectLowValue;}
                        if (yskm.equals("项目立项金额:总金额")){money = projectTotalAmount;}
                        ysxmdb.put("sxmainid",transaction.getString("id"));
                        JSONObject res1 = saveDt(ysxmdb, money, yskm);
                        project.put("isSuccess",res1.getBoolean("isSuccess"));
                        project.put("msg",res1.getString("msg"));
                        if (!res1.getBoolean("isSuccess")){
                            break;
                        }
                    }
                    data.add(project);
                } else  //年度预算调整接口
                    if (project.getString("flag").equals("1")){

                        ArrayList<String> list = new ArrayList<>();
                        list.add("项目预算:资本化");
                        list.add("项目预算:总金额");
                        list.add("项目预算:系统运营费");
                        list.add("项目预算:技术开发类费用");
                        list.add("项目预算:修理维护费");
                        list.add("项目预算预算:低值易耗品摊销");
                        //业务类型
                        String ywlx = number.getString("ywlx");

                        //业务细类
                        String ywxl = number.getString("ywxl");

//                        //新增明细
                        JSONObject object = new JSONObject();
                        object.put("ywlx",ywlx);
                        object.put("ywxl",ywxl);
                        object.put("sxnh",yssx);
                        object.put("sxmc",transaction.getString("sxmc"));
                        object.put("zdbz",transaction.getString("zdbz"));
                        object.put("xmbm",projectNumber);
                        object.put("xmmc",project.getString("xmmc"));
                        object.put("sxmainid",transaction.getString("id"));
                        object.put("xmmainid",number.getString("id"));
//                        object.put("mainid",id);
                        JSONObject anInterface = annualBudgetAdjustmentInterface(object, list, project);
                        data.add(anInterface);
                    } else if (project.getString("flag").equals("3")){
                        HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                        stringObjectHashMap.put("xmbm",number.getString("xmbm"));
                        stringObjectHashMap.put("xmmc",number.getString("xmmc"));
                        stringObjectHashMap.put("sqsj",TimeUtil.getCurrentDateString());
                        //事项编码
                        stringObjectHashMap.put("yssx",yssx);
                        JSONObject object = getTransactionBYId(yssx);
                        //事项名称
                        String sxmc = object.getString("sxmc");
                        stringObjectHashMap.put("sxmc",sxmc);
                        stringObjectHashMap.put("xmmainid",number.getString("id"));
                        ArrayList<JSONObject> id = selectUfYSXMDBByProjectNumberList(number.getString("id"));
                        saveCloseDt(stringObjectHashMap,id);
                    }
//                project.put("isSuccess",true);
//                project.put("msg","ok");
//                data.add(project);
            }

            result.put("status","0");
            result.put("errorCode","10000");
            result.put("errorMessage",null);
            result.put("data",data);
            result.put("result",true);
            result.put("message","ok");
            res.put("result",result);
        }

        return res;
    }


    public void saveCloseDt(Map<String,Object> map,List<JSONObject> objectList){
        for (JSONObject jsonObject1 : objectList) {
            LocalDate now = LocalDate.now();
            int year = now.getYear();
            String yskm = jsonObject1.getString("yskm");
            JSONObject jsonObject = selectUfYSXMDBByProjectNumber1(jsonObject1.getString("yskm"), year +"");
            BigDecimal decimal = jsonObject.getBigDecimal("ysye").multiply(BigDecimal.valueOf(-1));
            BigDecimal nddzyslx = decimal.add(jsonObject.getBigDecimal("nddzyslx"));
            BigDecimal nddzysnz = jsonObject.getBigDecimal("nddzysnz");
            BigDecimal ndqcys = jsonObject.getBigDecimal("ndqcys");

            //年度预算=年度期初预算+年度调整预算_年中+年度调整预算_零星；
            BigDecimal add = nddzyslx.add(nddzysnz).add(ndqcys);

            BigDecimal zys = jsonObject.getBigDecimal("zys");
            BigDecimal zxs = jsonObject.getBigDecimal("zxs");
            //发生总额=执行数+占用数；
            BigDecimal add1 = zys.add(zxs);

            //预算余额=年度预算-发生总额；
            BigDecimal subtract = add.subtract(add1);

            ArrayList<Object> list2 = new ArrayList<>();
            list2.add(nddzyslx);
            list2.add(add);
            list2.add(add1);
            list2.add(subtract);
            list2.add(year);
            list2.add(yskm);
            //向【项目底表年度明细表】推数，更新“【项目.年度预算-调整额】+年度调整预算→年度调整预算”；
            String sql2 = "update uf_ysxmdb_dt1 set nddzyslx = ?,ndys=?,fsze=?,ysye=? where ysnd = ? and yskm = ?";
            RecordSet rs2 = new RecordSet();
            rs2.executeUpdate(sql2, list2);


            JSONObject yszxdb = getYszxdb(yskm, year);


            BigDecimal dzyslx = decimal.add(yszxdb.getBigDecimal("dzyslx"));
            BigDecimal dzysnz = yszxdb.getBigDecimal("dzysnz");
            BigDecimal ndys2 = yszxdb.getBigDecimal("ndys");
            //可用预算总额=年度预算+调整预算_年中+调整预算_零星；
            BigDecimal add6 = ndys2.add(dzysnz).add(dzyslx);

            BigDecimal zyys = yszxdb.getBigDecimal("zyys");
            BigDecimal zhys = yszxdb.getBigDecimal("zhys");
            //发生总额=占用预算+执行预算；
            BigDecimal add7 = zyys.add(zhys);
            //预算余额=可用预算总额-发生总额；
            BigDecimal subtract2 = add6.subtract(add7);

            ArrayList<Object> list3 = new ArrayList<>();
            list3.add(dzyslx);
            list3.add(add6);
            list3.add(add7);
            list3.add(subtract2);
            list3.add(year);
            list3.add(yskm);
            //向【预算执行底表】推数，更新“【项目.年度预算-调整额】+【调整预算-零星】→【调整预算-零星】”
            String sql3 = "update uf_yszxdb set dzyslx = ?,kyysze=?,fsze=?,ysye=? where ysnd = ? and yskm = ?";
            RecordSet rs3 = new RecordSet();
            rs3.executeUpdate(sql3, list3);

            JSONObject transactionDt1 = getTransactionDt1(yskm, year + "");
            //年度已分配项目预算
            BigDecimal ndyfpxmys = transactionDt1.getBigDecimal("ndyfpxmys").add(decimal);
            BigDecimal ndys = transactionDt1.getBigDecimal("ndys");
            //年度剩余可分配预算=年度预算-年度已分配项目预算；
            BigDecimal subtract1 = ndys.subtract(ndyfpxmys);

            ArrayList<Object> list1 = new ArrayList<>();
            list1.add(ndyfpxmys);
            list1.add(subtract1);
            list1.add(year);
            list1.add(yskm);
            //向【事项底表年度明细表】推数，更新“【项目.年度预算-调整额】+年度已分配项目预算→年度已分配项目预算”；
            String sql1 = "update uf_yssxdb_dt1 set ndyfpxmys = ?,ndsykfpys=? where ysnd = ? and yskm = ?";
            RecordSet rs1 = new RecordSet();
            rs1.executeUpdate(sql1, list1);


            ArrayList<Object> list4 = new ArrayList<>();
            list4.add("是");
            list4.add(map.get("xmbm"));
            //向【项目底表】推数，当关闭/逆关闭=关闭时，更新“【是否关闭】=是”；
            String sql4 = "update uf_ysxmdb set sfgb = ? where xmbm = ?";
            RecordSet rs4 = new RecordSet();
            rs4.executeUpdate(sql4, list4);
        }

    }


    /**
     *  查询事项明细2表 根据预算科目
     */

    public JSONObject getTransactionDt2(String yskm,String mainid){
        ArrayList<String> strings = new ArrayList<>();
        strings.add(yskm);
        strings.add(mainid);
        String sql = "select * from uf_yssxdb_dt2 where yskm = ? and mainid = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
        JSONObject res = new JSONObject();
        while (rs.next()){
            //全周期事项总额
            res.put("qzqsxze",StringUtils.isEmpty(rs.getString("qzqsxze")) ? "0": rs.getString("qzqsxze"));
            //全周期调整额度
            res.put("qzqdzed",StringUtils.isEmpty(rs.getString("qzqdzed")) ? "0": rs.getString("qzqdzed"));
            res.put("qzqylxje",StringUtils.isEmpty(rs.getString("qzqylxje")) ? "0": rs.getString("qzqylxje"));
            res.put("qzqqcze",StringUtils.isEmpty(rs.getString("qzqqcze")) ? "0": rs.getString("qzqqcze"));
            res.put("zhs",StringUtils.isEmpty(rs.getString("zhs")) ? "0": rs.getString("zhs"));
            res.put("zys",StringUtils.isEmpty(rs.getString("zys")) ? "0": rs.getString("zys"));
        }
        return res;
    }

    /**
     *  查询事项明细1表 根据预算科目
     */

    public JSONObject getTransactionDt1(String yskm,String ysnd){
        ArrayList<String> strings = new ArrayList<>();
        strings.add(yskm);
        strings.add(ysnd);
        String sql = "select * from uf_yssxdb_dt1 where yskm = ? and ysnd = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
        JSONObject res = new JSONObject();
        while (rs.next()){
            //全周期事项总额
            res.put("ndys",StringUtils.isEmpty(rs.getString("ndys")) ? "0" : rs.getString("ndys"));
            res.put("ysye",StringUtils.isEmpty(rs.getString("ysye")) ? "0" : rs.getString("ysye") );
            res.put("nddzyslx",StringUtils.isEmpty(rs.getString("nddzyslx")) ? "0" : rs.getString("nddzyslx") );
            res.put("nddzysnz",StringUtils.isEmpty(rs.getString("nddzysnz")) ? "0" : rs.getString("nddzysnz") );
            res.put("ndqcys",StringUtils.isEmpty(rs.getString("ndqcys")) ? "0" : rs.getString("ndqcys") );
            res.put("ndyfpxmys",StringUtils.isEmpty(rs.getString("ndyfpxmys")) ? "0" : rs.getString("ndyfpxmys") );
            res.put("zhs",StringUtils.isEmpty(rs.getString("zhs")) ? "0" : rs.getString("zhs") );
            res.put("zys",StringUtils.isEmpty(rs.getString("zys")) ? "0" : rs.getString("zys") );
//            res.put("ndyfpxmys",StringUtils.isEmpty(rs.getString("ndyfpxmys")) ? "0" : rs.getString("ndyfpxmys") );
//            res.put("ysye",rs.getString("ysye"));
        }
        return res;
    }

    //查询事项表
    public JSONObject getTransactionBYId(String num){
        String sql = "select * from uf_yssxdb where sxbm = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, num);
        JSONObject res = new JSONObject();
        while (rs.next()){
            //事项编码
            res.put("id",rs.getString("id"));
            res.put("sxbm",rs.getString("sxbm"));
            //事项名称
            res.put("sxmc",rs.getString("sxmc"));
            res.put("zdbz",rs.getString("zdbz"));
        }
        return res;
    }

    //年度预算调整接口
    public JSONObject annualBudgetAdjustmentInterface(Map<String,Object> jsonObject,List<String> km,JSONObject project){
        boolean isSuccess = true;
        String msg = "ok";

        for (String s : km) {
            LocalDate now = LocalDate.now();
            int year = now.getYear();
            JSONObject req = selectUfYSXMDBByProjectNumber1(s,year +"");

            JSONObject ysxmdb2 = selectUfYSXMDBByProjectNumber2(s, (String) jsonObject.get("xmmainid"));
            //年度预算-调整前 - 项目
            BigDecimal ndys = req.getBigDecimal("ndys");
//            map.put("ndysdzqxm",ndys);
            //年度预算 - 调整后 - 项目
            BigDecimal bgtCapitalization = null;
            if (s.equals("项目预算:总金额")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("projectBgtAmount");
            }
            if (s.equals("项目预算:资本化")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("bgtCapitalization");
            }
            if (s.equals("项目预算:系统运营费")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("bgtSystemCosts");
            }
            if (s.equals("项目预算:技术开发类费用")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("bgtResearchDev");
            }
            if (s.equals("项目预算:修理维护费")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("bgtRepair");
            }
            if (s.equals("项目预算预算:低值易耗品摊销")){
                //年度预算-调整后
                bgtCapitalization = project.getBigDecimal("bgtLowValue");
            }

            //年度预算-调整额-项目 、全周期事项总额-调整额 、年度预算-调整额-事项
            BigDecimal decimal1 = bgtCapitalization.subtract(ndys);
            //全周期事项总额 - 调整前
            JSONObject object = getTransactionDt2(s, (String) jsonObject.get("sxmainid"));
            if (object.isEmpty()){
                isSuccess =false;
                msg = "查询查询事项全周期明细表失败 未知科目或事项";
                break;
            }
            //全周期调整额度
            BigDecimal qzqdzed = object.getBigDecimal("qzqdzed");
//            map.put("qzqsxze",object.get("qzqsxze"));
            //全周期事项总额 调整后
            BigDecimal qzqsxzedzh = object.getBigDecimal("qzqsxze").add(decimal1);

            JSONObject dt1 = getTransactionDt1(s, year +"");
            if (dt1.isEmpty()){
                isSuccess =false;
                msg = "查询查询事项年度明细表失败 未知科目或事项";
                break;
            }
            //年度预算-调整前 - 事项
//            BigDecimal ndysdzqsx = dt1.getBigDecimal("ndys");
            //年度预算-调整后 - 事项
            BigDecimal ndysdzhsx = dt1.getBigDecimal("ndys").add(decimal1);

            if (qzqsxzedzh.compareTo(ndysdzhsx) < 0){
                isSuccess =false;
                msg = "全周期事项总额-调整后，不得小于事项.年度预算-调整后";
                break;
            }
            BigDecimal fsze = req.getBigDecimal("fsze");
            if (bgtCapitalization.compareTo(fsze) < 0){
                isSuccess =false;
                msg = "全项目.年度预算-调整后，不得小于事项 发生总额";
                break;
            }

            if (ndysdzhsx.compareTo(bgtCapitalization) < 0){
                isSuccess =false;
                msg = "事项.年度预算-调整后 不得小于事项 项目.年度预算-调整后";
                break;
            }
            if (decimal1.add(dt1.getBigDecimal("ndyfpxmys")).compareTo(ndysdzhsx) > 0){
                isSuccess =false;
                msg = "项目【年度预算-调整额】+事项底表年度预算.年度已分配项目预算 不得大于 事项【年度预算-调整后】";
                break;
            }
            String xmbm = (String) jsonObject.get("xmbm");
            BigDecimal budgetExecution = getBudgetExecution(s, xmbm, year);
            //5.每一行，项目【年度预算-调整后】 <=全周期项目总额-（从预算执行底表查询sum(预算科目&预算项目对应的发生总额 where  预算年度不等于当前年度)）
            BigDecimal qzqxmze = ysxmdb2.getBigDecimal("qzqxmze");
            if (bgtCapitalization.compareTo(qzqxmze.subtract(budgetExecution)) > 0){
                isSuccess =false;
                msg = "项目【年度预算-调整后】 不得大于 全周期项目总额-发生总额";
                break;
            }

            //每一行，事项【全周期事项总额-调整后】＞＝（【事项.全周期累计已执行金额】+【事项.全周期累计已占用金额】） 事项.全周期累计已占用金额 没有字段
//            if (qzqsxzedzh.compareTo()){
//                isSuccess =false;
////                msg = "项目【年度预算-调整额】+事项底表年度预算.年度已分配项目预算 不得大于 事项【年度预算-调整后】";
//                continue;
//            }

            // 每一行，项目【年度预算-调整后】 <=全周期项目总额；
            if (bgtCapitalization.compareTo(qzqxmze) > 0){
                isSuccess =false;
                msg = "项目【年度预算-调整后】不得大于 全周期项目总额";
                break;
            }

            // 8.事项【全周期事项总额-调整后】  >= 事项底表.全周期已立项金额
            BigDecimal qzqylxje = object.getBigDecimal("qzqylxje");
            if (qzqsxzedzh.compareTo(qzqylxje) < 0){
                isSuccess =false;
                msg = "事项【全周期事项总额-调整后】  >= 事项底表.全周期已立项金额";
                break;
            }

            JSONObject yszxdb = getYszxdb(s, year);
            if (yszxdb.isEmpty()){
                isSuccess =false;
                msg = "查询预算执行表失败 未知科目或年度";
                break;
            }
            //	全周期事项总额=全周期期初总额+全周期调整额度；
            BigDecimal qzqqcze = object.getBigDecimal("qzqqcze");
            //全周期调整额度
            BigDecimal qzqdzed1 = qzqdzed.add(decimal1);
            //全周期事项总额
            BigDecimal add = qzqqcze.add(qzqdzed1);

            //全周期剩余可立项金额
            BigDecimal qzqylxje1 = add.subtract(object.getBigDecimal("qzqylxje"));

            BigDecimal zhs = object.getBigDecimal("zhs");
            BigDecimal zys = object.getBigDecimal("zys");
            //全周期累计已执行金额
            BigDecimal add1 = zhs.add(zys);
            //全周期剩余可执行金额
            BigDecimal subtract = add.subtract(add1);
            ArrayList<Object> list = new ArrayList<>();
            list.add(qzqdzed1);
            list.add(add);
            list.add(qzqylxje1);
            list.add(add1);
            list.add(subtract);
            list.add(jsonObject.get("sxmainid"));
            list.add(s);
            //向【事项底表全周期明细表】推数，更新“【全周期事项总额-调整额】+全周期调整额度→全周期调整额度”
            String sql = "update uf_yssxdb_dt2 set qzqdzed = ?,qzqsxze=?,qzqsyklxje=?,qzqljyzhje=?,qzqsykzhje=? where mainid = ? and yskm = ?";
            RecordSet rs = new RecordSet();
            rs.executeUpdate(sql, list);


            //年度调整预算_零星
            BigDecimal nddzyslx = dt1.getBigDecimal("nddzyslx").add(decimal1);
            //年度已分配项目预算
            BigDecimal ndyfpxmys = dt1.getBigDecimal("ndyfpxmys").add(decimal1);
            //年度期初预算
            BigDecimal ndqcys = dt1.getBigDecimal("ndqcys");
            //年度调整预算_年中
            BigDecimal nddzysnz = dt1.getBigDecimal("nddzysnz");
            //年度预算
            BigDecimal decimal = ndqcys.add(nddzysnz).add(nddzyslx);
            //年度剩余可分配预算
            BigDecimal add2 = decimal.add(ndyfpxmys);
            //发生总额
            BigDecimal add3 = dt1.getBigDecimal("zhs").add(dt1.getBigDecimal("zys"));
            //预算余额
            BigDecimal subtract1 = decimal.subtract(add3);
            ArrayList<Object> list1 = new ArrayList<>();
            list1.add(nddzyslx);
            list1.add(ndyfpxmys);
            list1.add(decimal);
            list1.add(subtract1);
            list1.add(add2);
            list1.add(year);
            list1.add(s);
            //向【事项底表年度明细表】推数，更新“【事项.年度预算-调整额】+年度调整预算_零星→年度调整预算_零星”
            //向【事项底表年度明细表】推数，更新“【项目.年度预算-调整额】+年度已分配项目预算→年度已分配项目预算”
            String sql1 = "update uf_yssxdb_dt1 set nddzyslx = ? ,ndyfpxmys = ?,ndys=? ,ndsykfpys=? ,ysye = ? where ysnd = ? and yskm = ?";
            RecordSet rs1 = new RecordSet();
            rs1.executeUpdate(sql1, list1);

            //年度调整预算零星
            BigDecimal nddzyslx1 = req.getBigDecimal("nddzyslx").add(decimal1);
            //年度期初预算
            BigDecimal ndqcys1 = req.getBigDecimal("ndqcys");
            //年度调整预算_年中
            BigDecimal nddzysnz1 = req.getBigDecimal("nddzysnz");
            //年度预算=年度期初预算+年度调整预算_年中+年度调整预算_零星；
            BigDecimal add4 = nddzyslx1.add(nddzysnz1).add(ndqcys1);

            BigDecimal zxs = req.getBigDecimal("zxs");
            BigDecimal zys1 = req.getBigDecimal("zys");
            //	发生总额=执行数+占用数；
            BigDecimal add5 = zxs.add(zys1);
            //预算余额=年度预算-发生总额；
            BigDecimal bigDecimal = add4.subtract(add5);

            ArrayList<Object> list2 = new ArrayList<>();
            list2.add(nddzyslx1);
            list2.add(add4);
            list2.add(add5);
            list2.add(bigDecimal);
            list2.add(year);
            list2.add(s);
            //向【项目底表年度明细表】推数，更新“【项目.年度预算-调整额】+年度调整预算→年度调整预算”
            String sql2 = "update uf_ysxmdb_dt1 set nddzyslx = ?,ndys=?,fsze=?,ysye=? where ysnd = ? and yskm = ?";
            RecordSet rs2 = new RecordSet();
            rs2.executeUpdate(sql2, list2);

            BigDecimal dzyslx = yszxdb.getBigDecimal("dzyslx").add(decimal1);
            BigDecimal dzysnz = yszxdb.getBigDecimal("dzysnz");
            BigDecimal ndys2 = yszxdb.getBigDecimal("ndys");
            //可用预算总额=年度预算+调整预算_年中+调整预算_零星；
            BigDecimal add6 = ndys2.add(dzysnz).add(dzyslx);

            BigDecimal zyys = yszxdb.getBigDecimal("zyys");
            BigDecimal zhys = yszxdb.getBigDecimal("zhys");

            //发生总额=占用预算+执行预算；
            BigDecimal add7 = zyys.add(zhys);

            //预算余额=可用预算总额-发生总额；
            BigDecimal subtract2 = add6.subtract(add7);
            ArrayList<Object> list3 = new ArrayList<>();
            list3.add(dzyslx);
            list3.add(add6);
            list3.add(add7);
            list3.add(subtract2);
            list3.add(year);
            list3.add(s);
            //向【预算执行底表】推数，更新“【项目.年度预算-调整额】+【调整预算-零星】→【调整预算-零星】”
            String sql3 = "update uf_yszxdb set dzyslx = ?,kyysze=?,fsze=?,ysye=? where ysnd = ? and yskm = ?";
            RecordSet rs3 = new RecordSet();
            rs3.executeUpdate(sql3, list3);
        }
        project.put("isSuccess",isSuccess);
        project.put("msg",msg);
        return project;

    }

    public BigDecimal getBudgetExecution(String km,String xmbh,Integer ysnd){

        String sql = "select sum(fsze) fsze from uf_yszxdb where yskm = ? and ysxm = ? and ysnd != ?";
        ArrayList<Object> strings = new ArrayList<>();
        strings.add(km);
        strings.add(xmbh);
        strings.add(ysnd);
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
        BigDecimal fsze = BigDecimal.ZERO;
        while (rs.next()){
            fsze = new BigDecimal(StringUtils.isEmpty(rs.getString("fsze")) ? "0" :rs.getString("fsze"));

        }
        return fsze;
    }

    public JSONObject getDzyslx(String km,Integer ysnd){

        JSONObject res = new JSONObject();
        String sql = "select *  from uf_yszxdb where yskm = ? and ysnd != ?";
        ArrayList<Object> strings = new ArrayList<>();
        strings.add(km);
        strings.add(ysnd);
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
//        BigDecimal dzyslx = BigDecimal.ZERO;
        while (rs.next()){
            res.put("dzyslx",StringUtils.isEmpty(rs.getString("dzyslx")) ? "0":rs.getString("dzyslx"));
        }
        return res;
    }
    public JSONObject getYszxdb(String km,Integer ysnd){

        JSONObject res = new JSONObject();
        String sql = "select *  from uf_yszxdb where yskm = ? and ysnd = ?";
        ArrayList<Object> strings = new ArrayList<>();
        strings.add(km);
        strings.add(ysnd);
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
//        BigDecimal dzyslx = BigDecimal.ZERO;
        while (rs.next()){
            res.put("dzyslx",StringUtils.isEmpty(rs.getString("dzyslx")) ? "0":rs.getString("dzyslx"));
            res.put("dzysnz",StringUtils.isEmpty(rs.getString("dzysnz")) ? "0":rs.getString("dzysnz"));
            res.put("ndys",StringUtils.isEmpty(rs.getString("ndys")) ? "0":rs.getString("ndys"));
            res.put("zyys",StringUtils.isEmpty(rs.getString("zyys")) ? "0":rs.getString("zyys"));
            res.put("zhys",StringUtils.isEmpty(rs.getString("zhys")) ? "0":rs.getString("zhys"));
            res.put("fsze",StringUtils.isEmpty(rs.getString("fsze")) ? "0":rs.getString("fsze"));
        }
        return res;
    }
    /**
     * 查询项目底表
     * @param projectNumber
     * @return
     */
    public JSONObject selectUfYSXMDBByProjectNumber(String projectNumber){
        String sql = "select * from uf_ysxmdb where xmbm = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, projectNumber);
        JSONObject jsonObject = new JSONObject();
        while (rs.next()){
            jsonObject.put("zxdw",rs.getString("zxdw"));
            jsonObject.put("id",rs.getString("id"));
            jsonObject.put("xmbm",rs.getString("xmbm"));
            jsonObject.put("xmmc",rs.getString("xmmc"));
            jsonObject.put("ywlx",rs.getString("ywlx"));
            jsonObject.put("ywxl",rs.getString("ywxl"));
            jsonObject.put("zz",rs.getString("zz"));
            jsonObject.put("hdnf",rs.getString("hdnf"));
            jsonObject.put("sfgb",rs.getString("sfgb"));
            jsonObject.put("qbmc",rs.getString("qbmc"));
            jsonObject.put("qbzje",rs.getString("qbzje"));
            jsonObject.put("xmzbrgh",rs.getString("xmzbrgh"));
            jsonObject.put("xmzbrmc",rs.getString("xmzbrmc"));
            jsonObject.put("xmqdsj",rs.getString("xmqdsj"));
            jsonObject.put("xmjssj",rs.getString("xmjssj"));
            jsonObject.put("szjyjhzd",rs.getString("szjyjhzd"));
            jsonObject.put("ywxmbm",rs.getString("ywxmbm"));
            jsonObject.put("ywxmmc",rs.getString("ywxmmc"));
            jsonObject.put("ywxmid",rs.getString("ywxmid"));
            jsonObject.put("xmmblx",rs.getString("xmmblx"));
            jsonObject.put("szywcp",rs.getString("szywcp"));
            jsonObject.put("modedatamodifier",rs.getString("modedatamodifier"));
            jsonObject.put("modedatamodifydatetime",rs.getString("modedatamodifydatetime"));
            jsonObject.put("yssx",rs.getString("yssx"));

        }
        return jsonObject;
    }
    /**
     * 查询项目底表明细2表
     * @return
     */
    public JSONObject selectUfYSXMDBByProjectNumber2(String yskm,String mainid){
        ArrayList<String> strings = new ArrayList<>();
        strings.add(yskm);
        strings.add(mainid);
        String sql = "select * from uf_ysxmdb_dt2 where yskm = ? and mainid = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
        JSONObject jsonObject = new JSONObject();
        while (rs.next()){
            jsonObject.put("yskm",rs.getString("yskm"));
            jsonObject.put("qzqqcze",StringUtils.isEmpty(rs.getString("qzqqcze")) ? "0" :rs.getString("qzqqcze"));
            jsonObject.put("qzqdzed",StringUtils.isEmpty(rs.getString("qzqdzed")) ? "0" :rs.getString("qzqdzed"));
            jsonObject.put("zxs",rs.getString("zxs"));
            jsonObject.put("zys",rs.getString("zys"));
            jsonObject.put("qzqljyzhje",StringUtils.isEmpty(rs.getString("qzqljyzhje")) ? "0":rs.getString("qzqljyzhje"));
            jsonObject.put("qzqsykzhje",StringUtils.isEmpty(rs.getString("qzqsykzhje"))?"0":rs.getString("qzqsykzhje"));
            jsonObject.put("qzqxmze",StringUtils.isEmpty(rs.getString("qzqxmze")) ? "0":rs.getString("qzqxmze"));
            jsonObject.put("mainid",rs.getString("mainid"));
        }
        return jsonObject;
    }
    /**
     * 查询项目底表明细1表
     * @return
     */
    public JSONObject selectUfYSXMDBByProjectNumber1(String yskm,String ysnd){

        ArrayList<String> strings = new ArrayList<>();
        strings.add(yskm);
        strings.add(ysnd);
        String sql = "select * from uf_ysxmdb_dt1 where yskm = ? and ysnd = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, strings);
        JSONObject jsonObject = new JSONObject();
        while (rs.next()){
            jsonObject.put("dwjzdgz",rs.getString("dwjzdgz"));
            jsonObject.put("ysnd",rs.getString("ysnd"));
            jsonObject.put("yskm",rs.getString("yskm"));
            jsonObject.put("ndqcys",rs.getString("ndqcys"));
            jsonObject.put("nddzysnz",StringUtils.isEmpty(rs.getString("nddzysnz")) ? "0":rs.getString("nddzysnz"));
            jsonObject.put("nddzyslx",StringUtils.isEmpty(rs.getString("nddzyslx"))?"0":rs.getString("nddzyslx"));
            jsonObject.put("ndys",StringUtils.isEmpty(rs.getString("ndys"))?"0":rs.getString("ndys"));
            jsonObject.put("zxs",rs.getString("zxs"));
            jsonObject.put("zys",rs.getString("zys"));
            jsonObject.put("fsze",rs.getString("fsze"));
            jsonObject.put("ysye",StringUtils.isEmpty(rs.getString("ysye"))?"0":rs.getString("ysye"));

        }
        return jsonObject;
    }
    /**
     * 查询项目底表明细1表
     * @return
     */
    public  ArrayList<JSONObject> selectUfYSXMDBByProjectNumberList(String mainid){

        String sql = "select * from uf_ysxmdb_dt1 where mainid = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, mainid);
        ArrayList<JSONObject> list = new ArrayList<>();
        while (rs.next()){
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("dwjzdgz",rs.getString("dwjzdgz"));
            jsonObject.put("ysnd",rs.getString("ysnd"));
            jsonObject.put("yskm",rs.getString("yskm"));
            jsonObject.put("ndqcys",rs.getString("ndqcys"));
            jsonObject.put("ndqcysnz",rs.getString("ndqcysnz"));
            jsonObject.put("ndqcyslx",rs.getString("ndqcyslx"));
            jsonObject.put("ndys",rs.getString("ndys"));
            jsonObject.put("zxs",rs.getString("zxs"));
            jsonObject.put("zys",rs.getString("zys"));
            jsonObject.put("fsze",rs.getString("fsze"));
            jsonObject.put("ysye",rs.getString("ysye"));
            list.add(jsonObject);
        }
        return list;
    }
    /**
     * 根据workflowid查询表名称
     * @param workflowid
     */
    public String getTableNameBYWorkflowid(Integer workflowid){

        String sql = "select t.id workflowid, t2.TABLENAME  from workflow_base t " +
                "left join workflow_bill t2 on t.FORMID = t2.id " +
                "WHERE t.id = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, workflowid);
        String tableName = null;
        while (rs.next()){
            tableName = rs.getString("TABLENAME");
        }
        return tableName;

    }


    /**
     * 新增项目变更明细表
     * @param param
     */
    public JSONObject saveDt(JSONObject param,BigDecimal projectCapitalization,String yskm){
        BigDecimal s = param.getBigDecimal("qzqxmze")== null ? BigDecimal.ZERO: param.getBigDecimal("qzqxmze");
        boolean isSuccess = true;
        JSONObject res = new JSONObject();
        String msg = "ok";
        //项目立项总金额-调整额
        BigDecimal subtract =projectCapitalization.subtract(s);
        JSONObject sxdt2 = getTransactionDt2(yskm, param.getString("sxmainid"));

        if (sxdt2.isEmpty()){
            res.put("isSuccess",false);
            res.put("msg","事项全周期明细数据异常，预算科目或事项错误");
            return res;
        }

        res.put("isSuccess",isSuccess);
        res.put("msg",msg);
        LocalDate now = LocalDate.now();
        int year = now.getYear();
        BigDecimal qzqljyzhje = param.getBigDecimal("qzqljyzhje");

        JSONObject ysxmdb1 = selectUfYSXMDBByProjectNumber1(yskm, String.valueOf(year));
        if (ysxmdb1.isEmpty()){
            res.put("isSuccess",false);
            res.put("msg","事项全年度明细数据异常，预算科目或年度错误");
            return res;
        }
        if (projectCapitalization.compareTo(qzqljyzhje.subtract(ysxmdb1.getBigDecimal("zys")).add(ysxmdb1.getBigDecimal("ndys"))) < 0){
            res.put("isSuccess",false);
            res.put("msg","【项目立项总金额-调整后】必须大于 (项目底表全周期明细表.全周期累计已执行金额-【当前年度的年度预算表.执行数】)+【当前年度的项目年度预算】");
            return res;
        }
        if (sxdt2.getBigDecimal("qzqsxze").subtract(sxdt2.getBigDecimal("qzqylxje")).add(subtract).compareTo(BigDecimal.ZERO) < 0){
            res.put("isSuccess",false);
            res.put("msg","事项底表.全周期事项总额-[全周期已立项金额+（项目立项总金额-调整额）]必须大于0");
            return res;
        }
        BigDecimal zxs = param.getBigDecimal("zxs");
        BigDecimal zys = param.getBigDecimal("zys");
        //全周期累计已执行金额
        BigDecimal decimal1 = zxs.add(zys);
        BigDecimal qzqqcze = param.getBigDecimal("qzqqcze");

        ArrayList<Object> list2 = new ArrayList<>();
        BigDecimal qzqdzed = param.getBigDecimal("qzqdzed");
        BigDecimal add = subtract.add(qzqdzed);
        //全周期项目总额
        BigDecimal decimal = qzqqcze.add(add);
        //全周期剩余可执行金额
        BigDecimal subtract1 = decimal.subtract(decimal1);
        list2.add(add);
        list2.add(decimal);
        list2.add(subtract1);
        list2.add(param.getString("mainid"));
        list2.add(yskm);
        //  向【项目底表全周期明细表】推数，更新“【项目立项总金额-调整额】+全周期调整额度→全周期调整额度”;
        //  全周期项目总额=全周期期初总额+全周期调整额度；
        //	全周期累计已执行金额=执行数+占用数；
        //	全周期剩余可执行金额=全周期项目总额-全周期累计已执行金额；
        String sql2 = "update uf_ysxmdb_dt2 set qzqdzed = ?,qzqxmze = ?,qzqsykzhje = ? where mainid = ? and yskm = ?";
        RecordSet rs2 = new RecordSet();
        rs2.executeUpdate(sql2, list2);

        //全周期已立项金额
        BigDecimal qzqylxje = subtract.add(sxdt2.getBigDecimal("qzqylxje"));
        //全周期事项总额
        BigDecimal qzqsxze = sxdt2.getBigDecimal("qzqsxze");
        //全周期剩余可立项金额
        BigDecimal subtract2 = qzqsxze.subtract(qzqylxje);

        ArrayList<Object> list = new ArrayList<>();
        list.add(qzqylxje);
        list.add(subtract2);
        list.add(param.getString("sxmainid"));
        list.add(yskm);
        //  向【事项底表全周期明细表】推数，更新“【项目立项总金额-调整额】+全周期已立项金额→全周期已立项金额”。
        //	全周期事项总额=全周期期初总额+全周期调整额度；（不用改）
        //	全周期剩余可立项金额=全周期事项总额-全周期已立项金额；
        //	全周期累计已执行金额=执行数+占用数；
        //	全周期剩余可执行金额=全周期事项总额-全周期累计已执行金额；
        String sql = "update uf_yssxdb_dt2 set qzqylxje = ?,qzqsyklxje = ? where mainid = ? and yskm = ?";
        RecordSet rs = new RecordSet();
        rs.executeUpdate(sql, list);

        return res;
    }
}
